On Supporting Hierarchical Data in Relational Main-Memory Database Systems

Hierarchical data is prevalent in a number of business use cases. However, most business data is stored in a relational database and hierarchical data is somehow encoded relationally. Since most queries on hierarchies also feature relational data, companies must continue maintaining their hierarchical data in a relational database system. There are many challenges when storing and querying hierarchical data in a relational context, because tables are inherently flat data structures while hierarchies possess inherent recursive properties. This impedance mismatch calls for dedicated hierarchy support in relational database systems: A user-friendly front end for querying hierarchical data and a sophisticated back end for evaluating these queries efficiently. In addition, hierarchies are usually dynamic with a high rate of complex, structural updates being issued on them. The aim of this thesis is to take on all these challenges. We propose a holistic framework for maintaining and querying hierarchical data in a modern main-memory relational database system. We design a data model for unifying hierarchical and relational data. Based on this model, we propose a query language, which blends seamlessly into SQL and allows the intuitive phrasing of queries working simultaneously on hierarchical and relational data. We then devise a family of indexing schemes to index hierarchical data effectively by enabling efficient queries and updates. We show how the proposed indexes can be used to answer queries in our SQL extension, thus providing all building blocks for our hierarchy framework. We then extend the framework with indexes for versioned hierarchical data, since many use cases require versioning for traceability and confirmability purposes. Finally, we propose an algorithm for the change recognition in hierarchies, which is important for versioning and completes our hierarchy framework. As our experiments on real-world data of SAP customers show, our framework enables unprecedented performance and is the first approach to handle complex updates efficiently. The fact that parts of our framework have already been shipped with the latest release of the SAP HANA Vora in-memory query engine [85] proves that our work is not only of theoretical scientific interest but adds real business value to a relational system.